#!/usr/bin python3
# -*- coding:UTF-8 -*-
# Author: nigo
import pandas as pd
import re
import datetime
import calendar
import os


class Bank:
    def __init__(self):
        # 序时账关键信息
        self.default_year = ""
        self.account_column_year = ""
        self.account_column_date = "" # 日期
        self.account_column_month = "" # 月
        self.account_column_day = "" # 日
        self.account_column_no = "" # 凭证号
        self.account_column_abstract = "" # 摘要
        self.account_column_in = "" # 借
        self.account_column_out = "" # 贷
        self.account_column_bank = "" # 银行账号

        # 银行流水关键信息
        self.bank_column_date = "" # 日期
        self.bank_column_no = "" # 银行账号
        self.bank_column_abstract = "" # 摘要
        self.bank_column_in = "" # 收
        self.bank_column_out = "" # 支
        self.bank_column_oppsite = "" # 对方账户名称
        self.df_bank = None # 银行流水
        self.df_account = None # 序时账
        self.distinct_accounts = None # 序时账中银行列表
        self.distinct_banks = None # 银行流水中银行列表
        self.data_account = {} # 由银行账号对应序时账组成的字典
        self.data_bank = {} # 由银行账号对应银行流水组成的字典
        self.match_account() # 初始化序时账表头关系对应
        self.match_bank() # 初始化银行流水表头关系对应

    def read_excel(self, path):
        """
        path:D:序时账路径
        sheet_name:表名
        return:dataframe types
        """
        df = pd.read_excel(path)
        df = df.fillna(0)
        df['match'] = ""
        return df

    def load_bank(self, path):
        """
        path:D:银行流水路径
        sheet_name:表名
        """
        self.df_bank = self.read_excel(path)

    def load_account(self, path):
        """
        path:序时账路径
        sheet_name:表名
        """
        self.df_account = self.read_excel(path)

    def match_account(self):
        """
        建立序时账表头对应关系
        """
        self.default_year = 2020
        self.account_column_day = "日"
        self.account_column_month = "月"
        self.account_column_no = "凭证号"
        self.account_column_abstract = "摘要"
        self.account_column_in = "借方本币"
        self.account_column_out = "贷方本币"
        self.account_column_bank = "辅助项"

    def match_bank(self):
        """
        建立银行流水表头对应关系
        """
        self.bank_column_date = "交易日期"
        self.bank_column_no = "账户号码"
        self.bank_column_abstract = "摘要"
        self.bank_column_in = "收入金额"
        self.bank_column_out = "支出金额"
        self.bank_column_oppsite = "对方账户名称"

    def clean_account_number(self):
        """清洗序时账的银行账号
        利用正则表达式提取出账号数字
        """
        for index, row in self.df_account.iterrows():
            no = row[self.account_column_bank] # 序时账表中列“辅助项”
            nos = re.findall('\d+', no) # 提取银行账号
            if nos:
                no = nos[0] # 匹配的第一个
            else:
                no = None
            self.df_account.loc[index, self.account_column_bank] = no

    def clean_date(self, df, column_name):
        """
        df:数据表
        column_name:日期列名
        return:df 清洗完成后的数据表
        清洗数据日期格式
        """
        for index, row in df.iterrows():
            match_str = re.search('(\d{4})[/-](\d{1,2})[/-](\d{1,2})',
                                  str(row[column_name]))
            match_tuple = match_str.group(1, 2, 3)
            number = tuple(map(int, match_tuple))
            df.loc[index, column_name] = datetime.date(number[0], number[1],
                                                       number[2])
        return df

    def combination_account_date(self):
        for index, row in self.df_account.iterrows():
            if self.account_column_year:
                self.df_account.loc[index,
                                    self.account_column_date] = datetime.date(
                                        int(row[self.account_column_year]),
                                        int(row[self.accoutn_column_month]),
                                        int(row[self.account_column_day]))
            else:
                self.df_account.loc[index,
                                    self.account_column_date] = datetime.date(
                                        int(self.default_year),
                                        int(row[self.account_column_month]),
                                        int(row[self.account_column_day]))

    def auto_clean_date(self):
        """
        将序时账和银行流水日期转化为标准日期格式
        """
        if self.account_column_date:
            self.clean_date(self.df_account, self.account_column_date)
        elif self.account_column_month != '' and self.account_column_day != '':
            self.df_account['日期'] = ''
            self.account_column_date = '日期'
            self.combination_account_date()
        if self.bank_column_date:
            self.clean_date(self.df_bank, self.bank_column_date)

    def clean_number(self, df, column_name, pattern):
        """清理替换字符串
        df:数据表
        column_name:列名
        pattern:特殊符号，用|隔开
        """
        pattern = '[%s]' % pattern
        for index, row in df.iterrows():
            df.loc[index, column_name] = re.sub(pattern, '', row[column_name])
        return df

    def auto_clean_number(self, pattern):
        """
        清洗银行流水账号的不干净字符
        pattern:特殊符号，用|隔开
        """
        self.df_bank = self.clean_number(self.df_bank, self.bank_column_no,
                                         pattern)

    def distinct_no(self):
        """
        获取序时账和银行流水的银行账号编码列表
        创建银行账号对应的dataframe字典
        （一个账号一个dataframe  self.data_bank,self.data_account
        """
        if self.df_account.empty == False and self.df_bank.empty == False:
            self.distinct_accounts = self.df_account[
                self.account_column_bank].unique() # 序时账银行列表
            self.distinct_banks = self.df_bank[
                self.bank_column_no].unique() # 资金流水银行列表
            self.distinct_all = list(
                set(self.distinct_accounts) & set(self.distinct_banks))
            self.distinct_diff = list(
                set(self.distinct_accounts) ^ set(self.distinct_banks))
            self.distinct_diff_bank = list(
                set(self.distinct_accounts) - set(self.distinct_banks))
            self.distinct_diff_account = list(
                set(self.distinct_banks) - set(self.distinct_accounts))
            for account in self.distinct_accounts:
                self.data_account[account] = self.df_account[self.df_account[
                    self.account_column_bank] == account] # 创建字典
            for bank in self.distinct_banks:
                self.data_bank[bank] = self.df_bank[self.df_bank[
                    self.bank_column_no] == bank] # 创建字典

    def myfloat(self, str):
        if str == '':
            return 0
        else:
            return float(str)

    def create_bank_date_index(self, df):
        """创建银行流水日期与索引的字典"""
        match_date = {} # 日期与dataframe索引的关系字典
        for index, row in df.iterrows():
            bank_date = row[self.bank_column_date] # 流水日期
            if bank_date in match_date.keys(): # 如果日期已经在字典中
                index_list = match_date[bank_date]
                index_list.append(index)
                match_date[bank_date] = index_list # 将索引添加到该日期的索引列表中
            else: # 如果日期不在字典中
                index_list = []
                index_list.append(index)
                match_date[bank_date] = index_list # 将该日期映射到该索引
        return match_date

    def get_bank_index(self, match_date, give_date, period, check_month):
        """给定序时账日期，需要核对的银行index
        match_date:银行流水日期与索引对应关系字典
        give_date:序时账指定日期
        period:期间
        check_month:是否按月核对
        return:index_list 需要核对的index列表
        """
        date_down = give_date - datetime.timedelta(days=period)
        date_up = give_date + datetime.timedelta(days=period)
        period_dates = [i.date() for i in pd.date_range(date_down, date_up)]
        if check_month:
            week, last_day = calendar.monthrange(give_date.year,give_date.month)# 返回当月第一天星期几，当月天数
            month_start = datetime.date(give_date.year, give_date.month, 1) # 当月第一天
            month_end = datetime.date(give_date.year, give_date.month,last_day) # 当月最后一天
            month_dates = [i.date() for i in pd.date_range(month_start, month_end)] # 当月所有日期列表
            period_dates = list(set(period_dates + month_dates)) # 期间与当月所有日期的合并
        index_list = []
        for day in period_dates:
            if day in match_date.keys():
                index_list += match_date[day] # 获取所有需要核对日期对应的数据索引
        return index_list

    def match(self,df_account,df_bank,tolerance=0.01,period=0,check_month=False):
        """
        df_account:单个账户序时账
        df_bank:单个账户银行流水
        tolerance:核对容错金额
        period:跨期核对天数
        check_month:在跨期天数的基础上全月范围内核对
        将匹配上的序时账和银行流水标记对方的索引号
        """
        match_date = self.create_bank_date_index(df_bank) # 日期与数据索引映射关系字典
        for index_a, account_row in df_account.iterrows(): # 遍历序时账数据
            # 序时账借方-贷方金额
            account_money = self.myfloat(account_row[self.account_column_in]) - self.myfloat(account_row[self.account_column_out])
            account_abstract = account_row[self.account_column_abstract] # 序时账摘要
            if self.account_column_date: # 如果序时账日期列名不为空
                account_date = account_row[self.account_column_date] # 获取序时账日期
            else:
                account_date = datetime.date( # 根据序时账月、日组合成日期
                    2020, int(account_row[self.account_column_month]),
                    int(account_row[self.account_column_day]
                        )) # ！注意当序时账没有日期，只有月份和日的时候，转换为日期
            bank_index_list = self.get_bank_index(match_date, account_date,period, check_month) # 获取日期对应需要核对的银行流水数据索引列表
            for index_b in bank_index_list: # 遍历需要核对的银行流水数据索引
                flag = self.df_bank.loc[index_b, 'match'] # flag为银行流水是否已经匹配标志
                if flag == "": # 如果未成功匹配过 则进行匹配操作
                    bank_money = self.myfloat( # 银行流水借方金额-贷方金额 
                        self.df_bank.loc[index_b, self.bank_column_in]-self.df_bank.loc[index_b, self.bank_column_out])
                    if abs(account_money - bank_money) <= tolerance: # 序时账金额与银行流水金额差异小于可容忍值
                        df_account.loc[index_a,'match'] = index_b # 序时账对应行记录银行流水匹配的索引号
                        df_bank.loc[index_b,'match'] = index_a # 银行流水对应行记录序时账匹配的索引号
                        self.df_account.loc[index_a, 'match'] = index_b # 序时账对应行记录银行流水匹配的索引号
                        self.df_bank.loc[index_b, 'match'] = index_a # 银行流水对应行记录序时账匹配的索引号
                        break
        result = { # 统计核对结果
            'account_in':df_account[self.account_column_in].sum(), # 序时账借方合计数
            'account_out':df_account[self.account_column_out].sum(), # 序时账贷方合计数
            'bank_in':df_bank[self.bank_column_in].sum(), # 银行流水借方合计数
            'bank_out':df_bank[self.bank_column_out].sum(), # 银行流水贷方合计数
            'match_account_in':df_account[df_account['match'] != ''][self.account_column_in].sum(), # 序时账匹配借方合计数
            'match_account_out':df_account[df_account['match'] != ''][self.account_column_out].sum(), # 序时账匹配贷方合计数
            'match_bank_in':df_bank[df_bank['match'] != ''][self.bank_column_in].sum(), # 银行流水匹配借方合计数
            'match_bank_out':df_bank[df_bank['match'] != ''][self.bank_column_out].sum(), # 银行流水匹配贷方合计数
        }
        return df_account, df_bank, result # 返回银行流水、序时账和统计结果

    def index2row(self):
        """将匹配的索引号转换为表格行号"""
        for index, row in self.df_account.iterrows():
            if row['match'] != '':
                self.df_account.loc[index, 'match'] += 1
        for index, row in self.df_bank.iterrows():
            if row['match'] != '':
                self.df_bank.loc[index, 'match'] += 1

    def match_all(self, tolerance=0, period=0, check_month=False):
        """多银行账号批量核对"""
        summary = {} # 核对结果信息
        total_accounts = len(self.distinct_accounts) # 银行账户数量
        count = 0
        for code in self.distinct_all: # 遍历所有银行账户
            count += 1
            print('正在测算账号', code, "进度{:.2%}".format(count / total_accounts))
            account = self.data_account[code] # 银行账号为code对应的序时账数据
            bank = self.data_bank[code] # 银行账号为code的银行流水数据
            df_account, df_bank, result = self.match(account,bank,tolerance=tolerance,period=period,check_month=check_month)
            summary[code] = result # 银行账号为code的核对统计信息
        df = pd.DataFrame(summary) # 将summary字典转换为dataframe类型
        return df.T # 返回转置后的df

    
    
if __name__ == "__main__":
    bank_path = '数据/银行流水.xlsx'
    account_path = '数据/序时账.xls'
    out_path = './结果.xlsx'
    bk =Bank() # 实例化Bank类
    bk.load_account(account_path) # 加载序时账数据
    bk.load_bank(bank_path) # 加载银行流水数据
    bk.clean_account_number() # 从序时账摘要中提取银行账号
    bk.auto_clean_date() # 对序时账和银行流水日期进行清洗
    bk.auto_clean_number(',') # 对银行流水账号中特殊符号进行清洗
    bk.distinct_no() # 计算序时账和银行流水中银行账号并集、交集、补集、差集
    result = bk.match_all(tolerance=0.05, period=31, check_month=True) # 多账号流水核对结果
    writer = pd.ExcelWriter(out_path) 
    bk.df_account.to_excel(excel_writer=writer, sheet_name='序时账') # 标记匹配字段的序时账保存到excel中
    bk.df_bank.to_excel(excel_writer=writer, sheet_name='银行流水') # 标记匹配字段的银行流水保存到excel中
    result.to_excel(excel_writer=writer, sheet_name='结果统计') # 统计结果保存到excel中
    df_diff = pd.concat([pd.DataFrame({'序时账缺失账号': bk.distinct_diff_account}),pd.DataFrame({'银行流水缺失账号': bk.distinct_diff_bank})],axis=1) # 缺失银行账号
    df_diff = df_diff.fillna('') # 将缺省值替换为''
    df_diff.to_excel(excel_writer=writer, sheet_name='缺失账号') # 将缺失账号保存到excel中
    writer.save() # 保存excel文档
    writer.close() # 关闭excel文档
